﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.SS.Util;
using NPOI.SS.Converter;
using NPOI.XSSF.Util;
using System.Drawing;
using AgingTest.DataProcess;
using System.IO;

namespace AgingTest.Tools
{
    public class ExcelExport
    {

        public XSSFWorkbook EstUserExport(TestData tempData, string Name)
        {

            string temp = "";
            int currRow = 0;
            int tempRow = 0;
            double temp1 = 0;
            int i1 = 0;
            string fileName = "土建核价单 " + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-ff");
            //创建07版Excel文件对象
            XSSFWorkbook book = new XSSFWorkbook();
            ISheet sheet1 = book.CreateSheet("汇总表");
            ISheet sheet2 = book.CreateSheet("上电检测");
            ISheet sheet3 = book.CreateSheet("寻找电机零位");
            ISheet sheet4 = book.CreateSheet("点动测试测试");
            ISheet sheet5 = book.CreateSheet("前置工况测试");
            ISheet sheet6 = book.CreateSheet("外特性测试");
            ISheet sheet7 = book.CreateSheet("速度精度测试");
            ISheet sheet8 = book.CreateSheet("位置精度测试");
            ISheet sheet9 = book.CreateSheet("特定工况测试");


            XSSFCellStyle fCellStyle = (XSSFCellStyle)book.CreateCellStyle();
            XSSFFont ffont = (XSSFFont)book.CreateFont();
            ffont.FontHeight = 220;
            ffont.FontName = "宋体";
            ffont.Color = new XSSFColor(Color.Black).Indexed;
            fCellStyle.SetFont(ffont);
            fCellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//垂直对齐
            fCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//水平对齐
            //IDataFormat format = book.CreateDataFormat();
            //fCellStyle.DataFormat = format.GetFormat("#,##0.00 ");//这是设置为3位数货币样式,#表示空位保留
            fCellStyle.BorderLeft = BorderStyle.Thin;
            fCellStyle.BorderRight = BorderStyle.Thin;
            fCellStyle.BorderTop = BorderStyle.Thin;
            fCellStyle.BorderBottom = BorderStyle.Thin;

            //汇总表部分      
            sheet1.SetColumnWidth(0, 5000);
            sheet1.SetColumnWidth(1, 5000);
            sheet1.SetColumnWidth(2, 5000);
            sheet1.SetColumnWidth(3, 5000);
            sheet1.SetColumnWidth(4, 5000);
            sheet1.SetColumnWidth(5, 5000);
            sheet1.SetColumnWidth(6, 5000);
            sheet1.SetColumnWidth(7, 5000);

            //第0行
            IRow row0 = sheet1.CreateRow(currRow);   //创建行
            IRow rowSum;
            currRow++;
            ICell cell = row0.CreateCell(0);                     //创建列

            temp = "测试结果  电机编号："+ Name;
            cell.SetCellValue(temp);
            setCellStyle(book, cell);
            sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 7));

            //第1行
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("测试结果");
            row0.CreateCell(1).SetCellValue(tempData.FinalProcess);
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue("");
            row0.CreateCell(7).SetCellValue("");
            sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 1, 7));

            //第2行
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("测试工序");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue("");
            row0.CreateCell(7).SetCellValue("");
            sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 7));

            //第3行
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("上电检测");
            row0.CreateCell(1).SetCellValue("寻找电机零位");
            row0.CreateCell(2).SetCellValue("点动测试测试");
            row0.CreateCell(3).SetCellValue("前置工况测试");
            row0.CreateCell(4).SetCellValue("外特性测试");
            row0.CreateCell(5).SetCellValue("速度精度测试");
            row0.CreateCell(6).SetCellValue("位置精度测试");
            row0.CreateCell(7).SetCellValue("特定工况测试");

            //第4行
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue(tempData.PowerOnCheck.errorCode.ToString("X2"));
            row0.CreateCell(1).SetCellValue(tempData.GetEleZero.errorCode.ToString("X2"));
            row0.CreateCell(2).SetCellValue(tempData.PointMoveTest.errorCode.ToString("X2"));
            row0.CreateCell(3).SetCellValue(tempData.Precondition.errorCode.ToString("X2"));
            row0.CreateCell(4).SetCellValue(tempData.ExteCharacter.errorCode.ToString("X2"));
            row0.CreateCell(5).SetCellValue(tempData.SpeedAccuracy.errorCode.ToString("X2"));
            row0.CreateCell(6).SetCellValue(tempData.PosAccuracy.errorCode.ToString("X2"));
            row0.CreateCell(7).SetCellValue(tempData.SpecCond.errorCode.ToString("X2"));

            //刷新各行格式
            ICell commCell;
            for (int i = 1; i < currRow; i++)
            {
                //Sheet1.GetRow(i).Height = 60 * 20;
                for (int j = 0; j < 8; j++)
                {

                    commCell = sheet1.GetRow(i).GetCell(j);

                    //CommCellStyle(book, commCell);
                    commCell.CellStyle = fCellStyle;

                }
            }

            sheet2 = PowerOnCheckSheet(tempData.PowerOnCheck, sheet2, fCellStyle);
            sheet3 = GetEleZeroSheet(tempData.GetEleZero, sheet3, fCellStyle);
            sheet4 = PointMoveTestSheet(tempData.PointMoveTest, sheet4, fCellStyle);
            sheet5 = PreconditionSheet(tempData.Precondition, sheet5, fCellStyle);
            sheet6 = ExteCharacterSheet(tempData.ExteCharacter, sheet6, fCellStyle);
            sheet7 = SpeedAccuracySheet(tempData.SpeedAccuracy, sheet7, fCellStyle);
            sheet8 = PosAccuracySheet(tempData.PosAccuracy, sheet8, fCellStyle);
            sheet9 = SpecCondSheet(tempData.SpecCond, sheet9, fCellStyle);

            return book;
        }

        /// <summary>
        /// 上电检测
        /// </summary>
        /// <param name="tempData"></param>
        /// <param name="Sheet1"></param>
        /// <param name="fCellStyle"></param>
        /// <returns></returns>
        public ISheet PowerOnCheckSheet(PowerOnCheckStruct tempData, ISheet Sheet1, XSSFCellStyle fCellStyle)
        {
            Sheet1.SetColumnWidth(0, 5000);
            Sheet1.SetColumnWidth(1, 5000);
            Sheet1.SetColumnWidth(2, 5000);
            Sheet1.SetColumnWidth(3, 5000);
            Sheet1.SetColumnWidth(4, 5000);
            Sheet1.SetColumnWidth(5, 5000);
            Sheet1.SetColumnWidth(6, 5000);
            Sheet1.SetColumnWidth(7, 5000);

            double Sum = 0;
            double tempSum = 0;
            int currRow = 0;
            bool tagTitle = true;//表面建立表头
            IRow rowSum;
            string Type = "";
            //设置表头
            IRow row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("工序名称：上电检测");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue("");
            row0.CreateCell(7).SetCellValue("");
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 7));

            row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("时间戳：" + tempData.timeRub.ToString() + ",电流值：" + tempData.eleCurrent.ToString("D") + "mA" + ",错误码：" + tempData.errorCode.ToString("X2"));
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue("");
            row0.CreateCell(7).SetCellValue("");
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 7));

            ICell commCell;
            for (int i = 1; i < currRow - 1; i++)
            {
                //Sheet1.GetRow(i).Height = 60 * 20;
                for (int j = 0; j < 8; j++)
                {

                    commCell = Sheet1.GetRow(i).GetCell(j);

                    //CommCellStyle(book, commCell);
                    commCell.CellStyle = fCellStyle;

                }
            }

            return Sheet1;

        }

        /// <summary>
        /// 寻找零位
        /// </summary>
        /// <param name="tempData"></param>
        /// <param name="Sheet1"></param>
        /// <param name="fCellStyle"></param>
        /// <returns></returns>
        public ISheet GetEleZeroSheet(GetEleZeroStruct tempData, ISheet Sheet1, XSSFCellStyle fCellStyle)
        {
            Sheet1.SetColumnWidth(0, 5000);
            Sheet1.SetColumnWidth(1, 5000);
            Sheet1.SetColumnWidth(2, 5000);
            Sheet1.SetColumnWidth(3, 5000);
            Sheet1.SetColumnWidth(4, 5000);
            Sheet1.SetColumnWidth(5, 5000);
            Sheet1.SetColumnWidth(6, 5000);
            Sheet1.SetColumnWidth(7, 5000);

            double Sum = 0;
            double tempSum = 0;
            int currRow = 0;
            bool tagTitle = true;//表面建立表头
            IRow rowSum;
            string Type = "";
            //设置表头
            IRow row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("工序名称：寻找电机零位");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue("");
            row0.CreateCell(7).SetCellValue("");
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 7));

            row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue(",错误码：" + tempData.errorCode.ToString("X2"));
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue("");
            row0.CreateCell(7).SetCellValue("");
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 7));

            ICell commCell;
            for (int i = 1; i < currRow - 1; i++)
            {
                //Sheet1.GetRow(i).Height = 60 * 20;
                for (int j = 0; j < 8; j++)
                {

                    commCell = Sheet1.GetRow(i).GetCell(j);

                    //CommCellStyle(book, commCell);
                    commCell.CellStyle = fCellStyle;

                }
            }

            return Sheet1;

        }



        /// <summary>
        /// 点动测试
        /// </summary>
        /// <param name="tempData"></param>
        /// <param name="Sheet1"></param>
        /// <param name="fCellStyle"></param>
        /// <returns></returns>
        public ISheet PointMoveTestSheet(PointMoveTestStruct tempData, ISheet Sheet1, XSSFCellStyle fCellStyle)
        {
            Sheet1.SetColumnWidth(0, 5000);
            Sheet1.SetColumnWidth(1, 5000);
            Sheet1.SetColumnWidth(2, 5000);
            Sheet1.SetColumnWidth(3, 5000);
            Sheet1.SetColumnWidth(4, 5000);
            Sheet1.SetColumnWidth(5, 5000);
            Sheet1.SetColumnWidth(6, 5000);
            Sheet1.SetColumnWidth(7, 5000);

            double Sum = 0;
            double tempSum = 0;
            int currRow = 0;
            bool tagTitle = true;//表面建立表头
            IRow rowSum;
            string Type = "";
            //设置表头
            IRow row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("工序名称：寻找电机零位");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue("");
            row0.CreateCell(7).SetCellValue("");
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 7));

            row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("时间戳");
            row0.CreateCell(1).SetCellValue("设定速度");
            row0.CreateCell(2).SetCellValue("实际速度");
            row0.CreateCell(3).SetCellValue("电流值(mA)");
            //row0.CreateCell(4).SetCellValue("");
            //row0.CreateCell(5).SetCellValue("");
            //row0.CreateCell(6).SetCellValue("");
            //row0.CreateCell(7).SetCellValue("");

            for (int i = 0; i < 6; i++)
            {
                row0 = Sheet1.CreateRow(currRow);   //创建行          
                currRow++;
                row0.CreateCell(0).SetCellValue(tempData.timeRub[i].ToString());
                row0.CreateCell(1).SetCellValue(tempData.getSpeed[i].setSpeed.ToString("D"));
                row0.CreateCell(2).SetCellValue(tempData.getSpeed[i].getSpeed.ToString("D"));
                row0.CreateCell(3).SetCellValue(tempData.eleCurrent[i].ToString("D"));

            }
            row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("错误码："+ tempData.errorCode.ToString("X2"));
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");




            ICell commCell;
            for (int i = 1; i < currRow - 1; i++)
            {
                //Sheet1.GetRow(i).Height = 60 * 20;
                for (int j = 0; j < 4; j++)
                {

                    commCell = Sheet1.GetRow(i).GetCell(j);

                    //CommCellStyle(book, commCell);
                    commCell.CellStyle = fCellStyle;

                }
            }

            return Sheet1;

        }


        /// <summary>
        /// 前置工况测试
        /// </summary>
        /// <param name="tempData"></param>
        /// <param name="Sheet1"></param>
        /// <param name="fCellStyle"></param>
        /// <returns></returns>
        public ISheet PreconditionSheet(PreconditionStruct tempData, ISheet Sheet1, XSSFCellStyle fCellStyle)
        {
            Sheet1.SetColumnWidth(0, 5000);
            Sheet1.SetColumnWidth(1, 5000);
            Sheet1.SetColumnWidth(2, 5000);
            Sheet1.SetColumnWidth(3, 5000);
            Sheet1.SetColumnWidth(4, 5000);
            Sheet1.SetColumnWidth(5, 5000);
            Sheet1.SetColumnWidth(6, 5000);
            Sheet1.SetColumnWidth(7, 5000);

            double Sum = 0;
            double tempSum = 0;
            int currRow = 0;
            bool tagTitle = true;//表面建立表头
            IRow rowSum;
            string Type = "";
            //设置表头
            IRow row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("工序名称：前置工况测试");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue("");
            row0.CreateCell(7).SetCellValue("");
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 7));

            row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("时间戳");
            row0.CreateCell(1).SetCellValue("设定速度");
            row0.CreateCell(2).SetCellValue("实际速度");
            row0.CreateCell(3).SetCellValue("电流值(mA)");
            row0.CreateCell(4).SetCellValue("温度°C");
            //row0.CreateCell(5).SetCellValue("");
            //row0.CreateCell(6).SetCellValue("");
            //row0.CreateCell(7).SetCellValue("");

            for (int i = 0; i < 10; i++)
            {
                row0 = Sheet1.CreateRow(currRow);   //创建行          
                currRow++;
                row0.CreateCell(0).SetCellValue(tempData.timeRub[i].ToString());
                row0.CreateCell(1).SetCellValue(tempData.getSpeed[i].setSpeed.ToString("D"));
                row0.CreateCell(2).SetCellValue(tempData.getSpeed[i].getSpeed.ToString("D"));
                row0.CreateCell(3).SetCellValue(tempData.eleCurrent[i].ToString("D"));
                row0.CreateCell(4).SetCellValue(tempData.temperature[i].ToString("D"));
            }
            row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("错误码：" + tempData.errorCode.ToString("X2"));
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");


            ICell commCell;
            for (int i = 1; i < currRow - 1; i++)
            {
                //Sheet1.GetRow(i).Height = 60 * 20;
                for (int j = 0; j < 5; j++)
                {

                    commCell = Sheet1.GetRow(i).GetCell(j);

                    //CommCellStyle(book, commCell);
                    commCell.CellStyle = fCellStyle;

                }
            }

            return Sheet1;

        }


        /// <summary>
        /// 外特性测试
        /// </summary>
        /// <param name="tempData"></param>
        /// <param name="Sheet1"></param>
        /// <param name="fCellStyle"></param>
        /// <returns></returns>
        public ISheet ExteCharacterSheet(ExteCharacterStruct tempData, ISheet Sheet1, XSSFCellStyle fCellStyle)
        {
            Sheet1.SetColumnWidth(0, 5000);
            Sheet1.SetColumnWidth(1, 5000);
            Sheet1.SetColumnWidth(2, 5000);
            Sheet1.SetColumnWidth(3, 5000);
            Sheet1.SetColumnWidth(4, 5000);
            Sheet1.SetColumnWidth(5, 5000);
            Sheet1.SetColumnWidth(6, 5000);
            Sheet1.SetColumnWidth(7, 5000);

            double Sum = 0;
            double tempSum = 0;
            int currRow = 0;
            bool tagTitle = true;//表面建立表头
            IRow rowSum;
            string Type = "";
            //设置表头
            IRow row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("工序名称：外特性测试");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue("");
            row0.CreateCell(7).SetCellValue("");
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 7));

            row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("时间戳");
            row0.CreateCell(1).SetCellValue("标准电机转速");
            row0.CreateCell(2).SetCellValue("设定转矩");
            row0.CreateCell(3).SetCellValue("实际转矩");
            row0.CreateCell(4).SetCellValue("电流值(mA)");
            row0.CreateCell(5).SetCellValue("温度°C");
            //row0.CreateCell(5).SetCellValue("");
            //row0.CreateCell(6).SetCellValue("");
            //row0.CreateCell(7).SetCellValue("");

            for (int i = 0; i < 30; i++)
            {
                row0 = Sheet1.CreateRow(currRow);   //创建行          
                currRow++;
                row0.CreateCell(0).SetCellValue(tempData.timeRub[i].ToString());
                row0.CreateCell(1).SetCellValue(tempData.eleStandardSpeed[i].ToString("D"));
                row0.CreateCell(2).SetCellValue(tempData.getTorque[i].setTorque.ToString("D"));
                row0.CreateCell(3).SetCellValue(tempData.getTorque[i].getTorque.ToString("D"));
                row0.CreateCell(4).SetCellValue(tempData.eleCurrent[i].ToString("D"));
                row0.CreateCell(5).SetCellValue(tempData.temperature[i].ToString("D"));
            }
            row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("错误码：" + tempData.errorCode.ToString("X2"));
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");

            ICell commCell;
            for (int i = 1; i < currRow - 1; i++)
            {
                //Sheet1.GetRow(i).Height = 60 * 20;
                for (int j = 0; j < 6; j++)
                {
                    commCell = Sheet1.GetRow(i).GetCell(j);
                    commCell.CellStyle = fCellStyle;
                }
            }

            return Sheet1;

        }


        /// <summary>
        /// 速度精度测试
        /// </summary>
        /// <param name="tempData"></param>
        /// <param name="Sheet1"></param>
        /// <param name="fCellStyle"></param>
        /// <returns></returns>
        public ISheet SpeedAccuracySheet(SpeedAccuracyStruct tempData, ISheet Sheet1, XSSFCellStyle fCellStyle)
        {
            Sheet1.SetColumnWidth(0, 5000);
            Sheet1.SetColumnWidth(1, 5000);
            Sheet1.SetColumnWidth(2, 5000);
            Sheet1.SetColumnWidth(3, 5000);
            Sheet1.SetColumnWidth(4, 5000);
            Sheet1.SetColumnWidth(5, 5000);
            Sheet1.SetColumnWidth(6, 5000);
            Sheet1.SetColumnWidth(7, 5000);

            double Sum = 0;
            double tempSum = 0;
            int currRow = 0;
            bool tagTitle = true;//表面建立表头
            IRow rowSum;
            string Type = "";
            //设置表头
            IRow row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("工序名称：速度精度测");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue("");
            row0.CreateCell(7).SetCellValue("");
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 7));

            row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("时间戳");
            row0.CreateCell(1).SetCellValue("设定速度");
            row0.CreateCell(2).SetCellValue("实际速度");
            row0.CreateCell(3).SetCellValue("被测电机转矩");
            row0.CreateCell(4).SetCellValue("电流值(mA)");

            //row0.CreateCell(5).SetCellValue("");
            //row0.CreateCell(6).SetCellValue("");
            //row0.CreateCell(7).SetCellValue("");

            for (int i = 0; i < 30; i++)
            {
                row0 = Sheet1.CreateRow(currRow);   //创建行          
                currRow++;
                row0.CreateCell(0).SetCellValue(tempData.timeRub[i].ToString());
                row0.CreateCell(1).SetCellValue(tempData.getSpeed[i].setSpeed.ToString("D"));
                row0.CreateCell(2).SetCellValue(tempData.getSpeed[i].getSpeed.ToString("D"));
                row0.CreateCell(3).SetCellValue(tempData.eleTorque[i].ToString("D"));
                row0.CreateCell(4).SetCellValue(tempData.eleCurrent[i].ToString("D"));
                
            }
            row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("错误码：" + tempData.errorCode.ToString("X2"));
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 4));

            ICell commCell;
            for (int i = 1; i < currRow - 1; i++)
            {
                //Sheet1.GetRow(i).Height = 60 * 20;
                for (int j = 0; j < 5; j++)
                {
                    commCell = Sheet1.GetRow(i).GetCell(j);
                    commCell.CellStyle = fCellStyle;
                }
            }

            return Sheet1;

        }

        /// <summary>
        /// 位置精度测试
        /// </summary>
        /// <param name="tempData"></param>
        /// <param name="Sheet1"></param>
        /// <param name="fCellStyle"></param>
        /// <returns></returns>
        public ISheet PosAccuracySheet(PosAccuracyStruct tempData, ISheet Sheet1, XSSFCellStyle fCellStyle)
        {
            Sheet1.SetColumnWidth(0, 5000);
            Sheet1.SetColumnWidth(1, 5000);
            Sheet1.SetColumnWidth(2, 5000);
            Sheet1.SetColumnWidth(3, 5000);
            Sheet1.SetColumnWidth(4, 5000);
            Sheet1.SetColumnWidth(5, 5000);
            Sheet1.SetColumnWidth(6, 5000);
            Sheet1.SetColumnWidth(7, 5000);

            double Sum = 0;
            double tempSum = 0;
            int currRow = 0;
            bool tagTitle = true;//表面建立表头
            IRow rowSum;
            string Type = "";
            //设置表头
            IRow row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("工序名称：位置精度测试");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue("");
            row0.CreateCell(7).SetCellValue("");
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 7));

            row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("时间戳");
            row0.CreateCell(1).SetCellValue("正反转方向:");
            row0.CreateCell(2).SetCellValue("设定位置");
            row0.CreateCell(3).SetCellValue("实际位置");


            //row0.CreateCell(5).SetCellValue("");
            //row0.CreateCell(6).SetCellValue("");
            //row0.CreateCell(7).SetCellValue("");


            row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue(tempData.timeRub.ToString());
            row0.CreateCell(1).SetCellValue("正转位置测量");
            row0.CreateCell(2).SetCellValue(tempData.posPosition.setPos.ToString("D"));
            row0.CreateCell(3).SetCellValue(tempData.posPosition.getPos.ToString("D"));

            row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue(tempData.timeRub.ToString());
            row0.CreateCell(1).SetCellValue("反转位置测量");
            row0.CreateCell(2).SetCellValue(tempData.negPosition.setPos.ToString("D"));
            row0.CreateCell(3).SetCellValue(tempData.negPosition.getPos.ToString("D"));


            row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("错误码：" + tempData.errorCode.ToString("X2"));
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");

            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 3));

            ICell commCell;
            for (int i = 1; i < currRow - 1; i++)
            {
                //Sheet1.GetRow(i).Height = 60 * 20;
                for (int j = 0; j < 4; j++)
                {
                    commCell = Sheet1.GetRow(i).GetCell(j);
                    commCell.CellStyle = fCellStyle;
                }
            }

            return Sheet1;

        }

        /// <summary>
        /// 特定工况测
        /// </summary>
        /// <param name="tempData"></param>
        /// <param name="Sheet1"></param>
        /// <param name="fCellStyle"></param>
        /// <returns></returns>
        public ISheet SpecCondSheet(SpecCondStruct tempData, ISheet Sheet1, XSSFCellStyle fCellStyle)
        {
            Sheet1.SetColumnWidth(0, 5000);
            Sheet1.SetColumnWidth(1, 5000);
            Sheet1.SetColumnWidth(2, 5000);
            Sheet1.SetColumnWidth(3, 5000);
            Sheet1.SetColumnWidth(4, 5000);
            Sheet1.SetColumnWidth(5, 5000);
            Sheet1.SetColumnWidth(6, 5000);
            Sheet1.SetColumnWidth(7, 5000);

            double Sum = 0;
            double tempSum = 0;
            int currRow = 0;
            bool tagTitle = true;//表面建立表头
            IRow rowSum;
            string Type = "";
            //设置表头
            IRow row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("工序名称：特定工况测试");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue("");
            row0.CreateCell(7).SetCellValue("");
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 7));

            row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue(",错误码：" + tempData.errorCode.ToString("X2"));
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue("");
            row0.CreateCell(7).SetCellValue("");
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 7));

            ICell commCell;
            for (int i = 1; i < currRow - 1; i++)
            {
                //Sheet1.GetRow(i).Height = 60 * 20;
                for (int j = 0; j < 8; j++)
                {

                    commCell = Sheet1.GetRow(i).GetCell(j);

                    //CommCellStyle(book, commCell);
                    commCell.CellStyle = fCellStyle;

                }
            }

            return Sheet1;

        }











        /// <summary>
        /// 合并单元格
        /// </summary>
        /// <param name="sheet">要合并单元格所在的sheet</param>
        /// <param name="rowstart">开始行的索引</param>
        /// <param name="rowend">结束行的索引</param>
        /// <param name="colstart">开始列的索引</param>
        /// <param name="colend">结束列的索引</param>
        public void SetCellRangeAddress(NPOI.SS.UserModel.ISheet sheet, int rowstart, int rowend, int colstart, int colend)
        {
            CellRangeAddress cellRangeAddress = new CellRangeAddress(rowstart, rowend, colstart, colend);
            sheet.AddMergedRegion(cellRangeAddress);
        }




        /// <summary>
        /// 设置标题单元格样式
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="cell"></param>
        public void setCellStyle(XSSFWorkbook workbook, ICell cell)
        {
            XSSFCellStyle fCellStyle = (XSSFCellStyle)workbook.CreateCellStyle();
            XSSFFont ffont = (XSSFFont)workbook.CreateFont();
            ffont.FontHeight = 15 * 20;
            ffont.FontName = "宋体";
            ffont.Color = new XSSFColor(Color.Black).Indexed;
            fCellStyle.SetFont(ffont);
            fCellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//垂直对齐
            fCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//水平对齐

            //fCellStyle.BorderLeft = BorderStyle.Thin;
            //fCellStyle.BorderRight = BorderStyle.Thin;
            //fCellStyle.BorderTop = BorderStyle.Thin;
            //fCellStyle.BorderBottom = BorderStyle.Thin;
            cell.CellStyle = fCellStyle;
        }

        public void CommCellStyle(XSSFWorkbook workbook, ICell cell)
        {
            XSSFCellStyle fCellStyle = (XSSFCellStyle)workbook.CreateCellStyle();
            XSSFFont ffont = (XSSFFont)workbook.CreateFont();
            ffont.FontHeight = 220;
            ffont.FontName = "宋体";
            ffont.Color = new XSSFColor(Color.Black).Indexed;
            fCellStyle.SetFont(ffont);
            fCellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//垂直对齐
            fCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//水平对齐
            IDataFormat format = workbook.CreateDataFormat();
            fCellStyle.DataFormat = format.GetFormat("#,##0.00 ");//这是设置为3位数货币样式

            fCellStyle.BorderLeft = BorderStyle.Thin;
            fCellStyle.BorderRight = BorderStyle.Thin;
            fCellStyle.BorderTop = BorderStyle.Thin;
            fCellStyle.BorderBottom = BorderStyle.Thin;
            cell.CellStyle = fCellStyle;
        }


    }



    //新建类 重写Npoi流方法
    public class NpoiMemoryStream : MemoryStream
    {
        public NpoiMemoryStream()
        {
            AllowClose = true;
        }

        public bool AllowClose { get; set; }

        public override void Close()
        {
            if (AllowClose)
                base.Close();
        }
    }


}
